package com.example.cuilei.sqlite;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.CursorAdapter;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

/**
 * Created by cuilei on 2014/12/9.
 */
public class MySQLiteActivity extends Activity {

    private Button btnAdd;
    private SQLiteDatabase db;
    private ListView lv;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite);
        //创建或建立数据库连接
        db = SQLiteDatabase.openOrCreateDatabase(this.getFilesDir().getPath() + "/my.db", null);
        lv = (ListView) findViewById(R.id.lvSongs);
        add(findViewById(R.id.btnAdd));

    }

    public void add(View view) {
        //获取用户输入
        String title = ((EditText) findViewById(R.id.txtGetSinger)).getText().toString();
        String user = ((EditText) findViewById(R.id.txtGetSongName)).getText().toString();
        try {
            insertDb(db,title,user);
            inflastList(selectDb());
        } catch (SQLiteException e){
            createDb();
            insertDb(db, title, user);
            inflastList(selectDb());
        }

    }

    //插入数据
    private void insertDb(SQLiteDatabase db, String title, String user) {
        db.execSQL("insert into song_inf values(null, ?, ?)", new String[]{title, user});
    }

    //查询数据
    private Cursor selectDb() {
        return db.rawQuery("select * from song_inf", null);
    }
    //创建表
    private void createDb(){
        db.execSQL("create table song_inf (_id integer" +
                " primary key autoincrement," +
                " song_title varchar(255)," +
                " song_user varchar(50))");
    }

    //显示列表项
    private void inflastList(Cursor cursor) {
        SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.simple_item, cursor,
                new String[]{"song_title", "song_user"},
                new int[]{R.id.txtSongName, R.id.txtSinger},
                CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);
        lv.setAdapter(adapter);
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        if (db!=null || db.isOpen()){
            db.close();
        }
    }
}
